﻿using System;
using System.Collections.Generic;
using System.Web;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Text;
using System.Collections;
using System.Web.Script.Serialization;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.Globalization;
/// <summary>
/// Summary description for ClassName
/// </summary>
public class UtilityManager
{
    private static int extCtr;
    
    public UtilityManager()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    
     public static bool GeneratePDF(string path){
        try{
            var doc = new Document();
            PdfWriter.GetInstance(doc,new FileStream(path,FileMode.Create));
            //work with the document
            doc.Open();
            doc.Add(new Paragraph("MyRemit Cash Card Management System"));
            doc.Close();
            return true;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static string GetPDFTemplateFieldNames(string path){
        try{
            PdfReader pr = new PdfReader(path);
            StringBuilder sb = new StringBuilder();
            foreach(var de in pr.AcroFields.Fields){
                sb.Append(de.Key.ToString() + Environment.NewLine);
            }
            
            return sb.ToString();
        }catch(Exception e){
            throw e;
        }
    }
    
    public static string FillCashCardTemplate(string path,dynamic c){
        try{
            string template = path+"/myRemit_app_form.pdf";
            string fileName = string.Format("{0}_{1}_{2}_app_form.pdf",c.FName,c.LName,c.CashCardAppNo);
            string newFile = path+"/"+fileName;
            PdfReader pdfReader = new PdfReader(template);
            PdfStamper pdfStamper = new PdfStamper(pdfReader,new FileStream(newFile,FileMode.Create));
            AcroFields pdfFormFields = pdfStamper.AcroFields;
            //car no fields
            //CC-001-000000213-249
            
            pdfFormFields.SetField("CashCardAppNo",c.CashCardAppNo.ToString());
            //card holder information
            //
            pdfFormFields.SetField("Country",c.Country.ToString());
            pdfFormFields.SetField("MonthArrived",c.MonthArrived.ToString());
            pdfFormFields.SetField("YearArrived",c.YearArrived.ToString());
            pdfFormFields.SetField("FName",c.FName.ToString());
            pdfFormFields.SetField("MName",c.MName.ToString());
            pdfFormFields.SetField("LName",c.LName.ToString());
            pdfFormFields.SetField("Bdate",c.Bdate.ToString("MM-dd-yyyy"));
            pdfFormFields.SetField("PlaceOfBirth",c.PlaceOfBirth.ToString());
            pdfFormFields.SetField("MotherMName",c.MotherMName.ToString());
            pdfFormFields.SetField("IDNumber",c.IDNumber.ToString());
            pdfFormFields.SetField("MobileNo",c.MobileNo.ToString().Replace("|",""));
            pdfFormFields.SetField("LandLineNo",c.LandLineNo.ToString().Replace("|",""));
            pdfFormFields.SetField("Email",c.Email.ToString());
            //get add1 and add2
            string[] currMailAdd = c.CurrMailAdd.Split('|');
            string[] permMailAdd = c.PermAdd.Split('|');
            string[] busMailAdd  = c.BusAdd.Split('|');
            //get nature of work
            var nows = RefDataManager.GetNatureOfWorkLkUpVal();
            string now = string.Empty;
            foreach(var work in nows){
                if(string.Compare(work.RefID,c.NatureOfWork)==0){
                    now=work.RefDesc;
                }
            }
            
            //pdfFormFields.SetField("CurrMailAdd",c.CurrMailAdd.ToString().Replace("|",""));
            pdfFormFields.SetField("CurrMailAdd",(currMailAdd[0]!="")?currMailAdd[0]:"");
            //pdfFormFields.SetField("CurrMailCity",c.CurrMailCity.ToString());
            pdfFormFields.SetField("CurrMailCity",(currMailAdd[1]!="")?currMailAdd[1]:"");
            pdfFormFields.SetField("CurrMailState",string.Format("{0} {1}",c.CurrMailCity.ToString(),c.CurrMailState.ToString()));
            pdfFormFields.SetField("CurrMailCountry",c.CurrMailCountry.ToString());
            pdfFormFields.SetField("CurrMailZip",c.CurrMailZip.ToString());
            pdfFormFields.SetField("PermAdd",(permMailAdd[0]!="")?permMailAdd[0]:"");
            pdfFormFields.SetField("PermAddState",string.Format("{0} {1}",c.PermAddCity.ToString(),c.PermAddState.ToString()));
            pdfFormFields.SetField("PermAddCity",(permMailAdd[1]!="")?permMailAdd[1]:"");
            pdfFormFields.SetField("PermAddCountry",c.PermAddCountry.ToString());
            //pdfFormFields.SetField("PermAddState",c.PermAddState.ToString());
            pdfFormFields.SetField("PermAddZip",c.PermAddZip.ToString());
            pdfFormFields.SetField("Employer",c.Employer.ToString());
            pdfFormFields.SetField("NatureOfWork",now);
            pdfFormFields.SetField("WorkPhone",c.WorkPhone.ToString().Replace("|",string.Empty));
            pdfFormFields.SetField("BusAdd",(busMailAdd[0]!="")?busMailAdd[0]:"");
            pdfFormFields.SetField("BusAddCity",(busMailAdd[1]!="")?busMailAdd[1]:"");
            pdfFormFields.SetField("BusAddState",string.Format("{0} {1}",c.BusAddCity.ToString(),c.BusAddState.ToString()));
            pdfFormFields.SetField("BusAddCountry",c.BusAddCountry.ToString());
            pdfFormFields.SetField("BusAddZip",c.BusAddZip.ToString());
            pdfFormFields.SetField("NameOnCard",c.NameOnCard.ToString());
           /* pdfFormFields.SetField("remCountry",c.remCountry);
            pdfFormFields.SetField("remMonArrived",c.remMonArrived);
            pdfFormFields.SetField("remYearArrived",c.remYearArrived);
            pdfFormFields.SetField("remFName",c.remFName);
            pdfFormFields.SetField("remMName",c.remMName);
            pdfFormFields.SetField("remLName",c.remLName);
            pdfFormFields.SetField("remBdate",c.remBdate);
            pdfFormFields.SetField("remRel2Ref",c.remRel2Ref);
            pdfFormFields.SetField("remMobileNo",c.remMobileNo);
            pdfFormFields.SetField("remLandLineNo",c.remLandLineNo);
            pdfFormFields.SetField("remCurrMailAdd",c.remCurrMailAdd);
            pdfFormFields.SetField("remCurrMailCity",c.remCurrMailCity);
            pdfFormFields.SetField("remCurrMailState",c.remCurrMailState);
            pdfFormFields.SetField("remCurrMailState",c.remCurrMailState);*/
            
            //for checkboxes 
            //check for CustomerClassification
            string custClass=c.CustomerClassification;
            if(custClass.Equals("OF") || custClass.Equals("SF")){
              pdfFormFields.SetField("chkOverseas","Yes");
                
            }
            if(custClass.Equals("InPH") || custClass.Equals("ABSCBNEmp")){
               pdfFormFields.SetField("chkPhil","Yes");
            }
            //check for gender
            string gender = c.Gender;
            if(gender.Equals("m")){
                pdfFormFields.SetField("chk_gender_m","Yes");
            }
            
            if(gender.Equals("f")){
                pdfFormFields.SetField("chk_gender_f","Yes");
            }
            
            //check nationality
            string nat = c.Nationality;
            if(nat.Equals("PH")){
                 pdfFormFields.SetField("chk_nat_filipino","Yes");
            }else{
                 pdfFormFields.SetField("chk_nat_others","Yes");
            }
            
            //check civil status
            string cs = c.CivilStatus;
            if(cs.Equals("Married")){
                 pdfFormFields.SetField("chk_cs_married","Yes");
            }else if(cs.Equals("Single")){
                 pdfFormFields.SetField("chk_cs_single","Yes");
            }else{
                pdfFormFields.SetField("chk_cs_others","Yes");
                
            }
            
            //check idtype
            var idTypes = RefDataManager.GetIDTypeLkUpVal();
            string idType = string.Empty;
            string idTypeCde = c.IDType;
            foreach(var idT in idTypes){
                if(string.Compare(idT.RefID,c.IDType)==0){
                    idType=idT.RefDesc;
                }
            }
            
            if(idTypeCde.Equals("07")){
                pdfFormFields.SetField("chk_idType_sss","Yes");
                pdfFormFields.SetField("chkIDTypeO","Yes");
                pdfFormFields.SetField("IDTypeOthers",idType);
                
            }else if(idTypeCde.Equals("08")){
                pdfFormFields.SetField("chk_idType_gsis","Yes");
                pdfFormFields.SetField("chkIDTypeO","Yes");
                pdfFormFields.SetField("IDTypeOthers",idType);
         
            }else if(idTypeCde.Equals("09")){
                 pdfFormFields.SetField("chk_idType_tin","Yes");
                 pdfFormFields.SetField("chkIDTypeO","Yes");
                 pdfFormFields.SetField("IDTypeOthers",idType);
         
            }else if(idTypeCde.Equals("03")){
                 pdfFormFields.SetField("chk_idType_pass","Yes");
                 pdfFormFields.SetField("chkIDTypePass","Yes");
                
                
            }
            
            //check source of funds
            string[] sofs = c.SrcOfFunds.Split(',');
            string salary = "01";
            string loanProceeds = "08";
            string remittance="06";
            string savings = "02";
            string ret = "07";
            string others ="08";
            
            //check if src of funds size is > 1
            if(sofs.Length >1){
                 foreach(string sof in sofs){
                    if(sof.Equals(salary)){
                        pdfFormFields.SetField("chk_sof_salary","Yes");
                    }
                    if(sof.Equals(loanProceeds)){
                        pdfFormFields.SetField("chk_sof_lp","Yes");
                    }
                    
                    if(sof.Equals(remittance)){
                        pdfFormFields.SetField("chk_sof_rem","Yes");
                    }
                    
                    if(sof.Equals(savings)){
                        pdfFormFields.SetField("chk_sof_savings","Yes");
                    }
                    
                    if(sof.Equals(ret)){
                        pdfFormFields.SetField("chk_sof_ret","Yes");
                    }
                    
                    if(sof.Equals(others)){
                        pdfFormFields.SetField("chk_sof_others","Yes");
                    }
              } 
            }else{
                string sof2 = c.SrcOfFunds;
                  if(sof2.Equals(salary)){
                        pdfFormFields.SetField("chk_sof_salary","Yes");
                    }
                    if(sof2.Equals(loanProceeds)){
                        pdfFormFields.SetField("chk_sof_lp","Yes");
                    }
                    
                    if(sof2.Equals(remittance)){
                        pdfFormFields.SetField("chk_sof_rem","Yes");
                    }
                    
                    if(sof2.Equals(savings)){
                        pdfFormFields.SetField("chk_sof_savings","Yes");
                    }
                    
                    if(sof2.Equals(ret)){
                        pdfFormFields.SetField("chk_sof_ret","Yes");
                    }
                    
                    if(sof2.Equals(others)){
                        pdfFormFields.SetField("chk_sof_others","Yes");
                    }
            }
           
           
            
            
            //check employment
            string[] emps = c.Employment.Split(',');
            if(emps.Length>1){
               
                foreach(string emp in emps){
                     if(emp.Equals("10")){
                        pdfFormFields.SetField("chk_emp_private","Yes");
                    }
                    if(emp.Equals("20")){
                        pdfFormFields.SetField("chk_emp_gov","Yes");
                    }
                    if(emp.Equals("02")){
                        pdfFormFields.SetField("chk_emp_self","Yes");
                    }
                    if(emp.Equals("03")){
                        pdfFormFields.SetField("chk_emp_ne","Yes");
                    }
                    if(emp.Equals("04")){
                        pdfFormFields.SetField("chk_emp_ret","Yes");
                    }
                 } 
            }else{
                string emp = c.Employment;
                    if(emp.Equals("10")){
                        pdfFormFields.SetField("chk_emp_private","Yes");
                    }
                    if(emp.Equals("20")){
                        pdfFormFields.SetField("chk_emp_gov","Yes");
                    }
                    if(emp.Equals("02")){
                        pdfFormFields.SetField("chk_emp_self","Yes");
                    }
                    if(emp.Equals("03")){
                        pdfFormFields.SetField("chk_emp_ne","Yes");
                    }
                    if(emp.Equals("04")){
                        pdfFormFields.SetField("chk_emp_ret","Yes");
                    }
            }
           
            
            //check designation
            string desig = c.Designation;
            if(desig.Equals("no")){
                pdfFormFields.SetField("chk_desig_staff","Yes");
            }
            
            if(desig.Equals("of")){
                 pdfFormFields.SetField("chk_desig_officer","Yes");
            }
            if(desig.Equals("exec")){
                 pdfFormFields.SetField("chk_desig_exec","Yes");
            }
            
            //check mode of remittance
            string mor = c.ModeOfRemit;
            if(mor.Equals("ol")){
                pdfFormFields.SetField("chk_mor_ol","Yes");
            }
            
            if(mor.Equals("bp")){
                pdfFormFields.SetField("chk_mor_phone","Yes");
            }
            
            if(mor.Equals("sms")){
                pdfFormFields.SetField("chk_mor_sms","Yes");
            }
            
            if(mor.Equals("wi")){
                pdfFormFields.SetField("chk_mor_wi","Yes");
            }
            
            if(mor.Equals("b2b")){
                pdfFormFields.SetField("chk_mor_bank","Yes");
            }
            
            if(mor.Equals("d2d")){
                pdfFormFields.SetField("chk_mor_d2d","Yes");
            }
            
            if(mor.Equals("cc")){
                pdfFormFields.SetField("chk_mor_cc","Yes");
            }
            
            if(mor.Equals("cp")){
                pdfFormFields.SetField("chk_mor_cash","Yes");
            }
            
            if(mor.Equals("ff")){
                pdfFormFields.SetField("chk_mor_ff","Yes");
            }
            
            //check remit freq
            string rf = c.RemitFrequency;
            if(rf.Equals("m2")){
                pdfFormFields.SetField("chk_rf_2m","Yes");
            }
            
            if(rf.Equals("m1")){
                pdfFormFields.SetField("chk_rf_1m","Yes");
            }
            
            if(rf.Equals("y1")){
                pdfFormFields.SetField("chk_rf_1y","Yes");
            }
            
            if(rf.Equals("y2")){
                pdfFormFields.SetField("chk_rf_2y","Yes");
            }
            
            if(rf.Equals("y3_4")){
                pdfFormFields.SetField("chk_rf_3x4y","Yes");
            }
            
             if(rf.Equals("y6")){
                pdfFormFields.SetField("chk_rf_6xy","Yes");
            }
            
            //call freq
            string cf = c["CallFrequency"];
             if(cf.Equals("daily")){
                pdfFormFields.SetField("chk_cf_daily","Yes");
            }
            
            if(cf.Equals("w1")){
                pdfFormFields.SetField("chk_cf_1w","Yes");
            }
            
            if(cf.Equals("w2")){
                pdfFormFields.SetField("chk_cf_2w","Yes");
            }
            
            if(cf.Equals("m3x4")){
                pdfFormFields.SetField("chk_cf_3x4m","Yes");
            }
            
            if(cf.Equals("m1")){
                pdfFormFields.SetField("chk_cf_1m","Yes");
            }
            
             if(cf.Equals("dncr")){
                pdfFormFields.SetField("chk_cf_dncr","Yes");
            }
            
            //call mode
            string cm = c["ModeOfCall"];
             if(cm.Equals("ldd")){
                pdfFormFields.SetField("chk_cm_ldd","Yes");
            }
            
            if(cm.Equals("mlsim")){
                pdfFormFields.SetField("chk_cm_mobile","Yes");
            }
            if(cm.Equals("ttnum")){
                pdfFormFields.SetField("chk_cm_1010","Yes");
            }
             if(cm.Equals("voipint")){
                pdfFormFields.SetField("chk_cm_voip","Yes");
            }
             if(cm.Equals("dpclu")){
                pdfFormFields.SetField("chk_cm_dpcl","Yes");
            }
            
            if(cm.Equals("dpcmu")){
                pdfFormFields.SetField("chk_cm_dpcm","Yes");
            }
            
            if(cm.Equals("rclu")){
                pdfFormFields.SetField("chk_cm_rcl","Yes");
            }
            if(cm.Equals("rcmu")){
                pdfFormFields.SetField("chk_cm_rcm","Yes");
            }
            
            //cargo freq
            string cargof = c.CargoFrequency;
             if(cargof.Equals("m2")){
                pdfFormFields.SetField("chk_cargof_2xm","Yes");
            }
            if(cargof.Equals("m1")){
                pdfFormFields.SetField("chk_cargof_1m","Yes");
            }
            if(cargof.Equals("y1")){
                pdfFormFields.SetField("chk_cargof_1y","Yes");
            }
            if(cargof.Equals("y2")){
                pdfFormFields.SetField("chk_cargof_2y","Yes");
            }
            if(cargof.Equals("y3x4")){
                pdfFormFields.SetField("chk_cargof_3x4xy","Yes");
            }
            
            if(cargof.Equals("y6x")){
                pdfFormFields.SetField("chk_cargof_6xy","Yes");
            }
            
            pdfStamper.FormFlattening = false;
            pdfStamper.Close();
            
            return fileName;
            
        }catch(Exception e){
            throw e;
        }
    }
    
    public static byte[] GenerateXLForDelivery(string path,string sh,IList<dynamic> customers){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=2;
           
            foreach(var cust in customers){
                int col=0;
               IRow dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                
                var customer = CustomerManager.GetCustomer(cust.CustomerID);
                
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardAppNo);
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0} {1}",cust.FName,cust.LName));
                //get the address
                string add = customer.CurrMailAdd.Replace("|"," ");
                dataRow.CreateCell(col++).SetCellValue(add);
                dataRow.CreateCell(col++).SetCellValue(customer.CurrMailCity);
                dataRow.CreateCell(col++).SetCellValue(customer.CurrMailState);
                dataRow.CreateCell(col++).SetCellValue(customer.Country);
                dataRow.CreateCell(col++).SetCellValue(customer.CurrMailZip);
                //dataRow.CreateCell(col++).SetCellValue(cust.CardDelStatus);
                dataRow.CreateCell(col++).SetCellValue(string.Empty);
                dataRow.CreateCell(col++).SetCellValue(DateTime.Parse(cust.DelStatusDate.ToString("MM-dd-yyyy")));
                //dataRow.CreateCell(col++).SetCellValue(DateTime.Parse(cust.DelDate.ToString("MM-dd-yyyy")));
                dataRow.CreateCell(col++).SetCellValue(string.Empty);
                
                /*  
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardStatus);
                dataRow.CreateCell(col++).SetCellValue(DateTime.Parse(cust.CashCardStatusDate.ToString()));
                dataRow.CreateCell(col++).SetCellValue(cust.DocumentStatus);
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardNo);
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardType);
                dataRow.CreateCell(col++).SetCellValue(cust.PartnerBank);
                dataRow.CreateCell(col++).SetCellValue(DateTime.Parse(cust.StatusDate.ToString()));
                dataRow.CreateCell(col++).SetCellValue(cust.NameOnCard);
                dataRow.CreateCell(col++).SetCellValue(cust.CardExpDate);
                dataRow.CreateCell(col++).SetCellValue(cust.CardDelCourier);
                */
                //dataRow.CreateCell(col++).SetCellValue(cust.Remarks);
                dataRow.CreateCell(col++).SetCellValue(cust.DelRemarks);
                //additional columns IDType, IDNumber , Document Status
               /* var idTypes = RefDataManager.GetIDTypeLkUpVal();
                string idType = string.Empty;
                foreach(var id in idTypes){
                    if(id.RefID.Equals(cust.IDType)){
                        idType = id.RefDesc;
                    }
                }*/
                if(string.Compare(cust.IDType,"default")==0){
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                }else{
                    dataRow.CreateCell(col++).SetCellValue(cust.IDType);
                }   
             
               
                dataRow.CreateCell(col++).SetCellValue(cust.IDNumber);
                dataRow.CreateCell(col++).SetCellValue(cust.DocumentStatus);
                dr++;
            }
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            
            
           
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }
    
    
     public static byte[] GenerateCCMasterListXL(string path,string sh,IList<dynamic> customers){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=1;
             IRow dataRow =null;
            foreach(var cust in customers){
                int col=0;
               dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0} {1}",cust.FName,cust.LName));
                dataRow.CreateCell(col++).SetCellValue(cust.CustomerStatus);
                string ccsd_duration = string.Empty;
                string ccdd_duration = string.Empty;
                if(cust.CashCardAppNo!=null){
                    dataRow.CreateCell(col++).SetCellValue(cust.CashCardAppNo);
                    ccsd_duration = UtilityManager.GetDuration(cust.CashCardStatusDate);
                    ccdd_duration = UtilityManager.GetDuration(cust.DelStatusDate);
                    dataRow.CreateCell(col++).SetCellValue(string.Format("{0}",ShowCCStatus(cust.CashCardStatus),ccsd_duration));
                    dataRow.CreateCell(col++).SetCellValue(string.Format("{0}",ShowDelStatus(cust.CardDelStatus,cust.DelStatusDate)));
                    if(string.Compare(cust.DelDate.ToString(),"1/1/1900 12:00:00 AM")==0){
                        dataRow.CreateCell(col++).SetCellValue(string.Empty);
                    }else{
                        dataRow.CreateCell(col++).SetCellValue(DateTime.Parse(cust.DelDate.ToString("MM-dd-yyyy")));
                        
                    }
                    
                    dataRow.CreateCell(col++).SetCellValue(cust.PartnerBank);
                    dataRow.CreateCell(col++).SetCellValue(cust.CardDelCourier);
                }else{
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                    
                }
               
                dataRow.CreateCell(col++).SetCellValue(cust.Region);
                dataRow.CreateCell(col++).SetCellValue(cust.Solicitor);
                dr++;
            }
            //get Total Records
            dataRow =sheet.GetRow(dr);
             if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
            
            dataRow.CreateCell(0).SetCellValue("Total Records:");
            dataRow.CreateCell(1).SetCellValue(customers.Count.ToString());
            
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            //autosize columns
            for(int i=0;i<9;i++){
                sheet.AutoSizeColumn(i,true);
            }
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }
    
    
     public static byte[] GenerateValidationListXL(string path,string sh,IList<dynamic> customers){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=2;
           IRow dataRow = null;
            foreach(var cust in customers){
                int col=0;
               dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0} {1}",cust.FName,cust.LName));
                dataRow.CreateCell(col++).SetCellValue(cust.CustomerStatus);
                dataRow.CreateCell(col++).SetCellValue(cust.Region);
                dataRow.CreateCell(col++).SetCellValue(cust.Solicitor);
                dataRow.CreateCell(col++).SetCellValue(cust.Campaign);
                dataRow.CreateCell(col++).SetCellValue(cust.Country);
                dataRow.CreateCell(col++).SetCellValue(cust.CurrMailState);
                if(cust.DocumentStatus!=null){
                  dataRow.CreateCell(col++).SetCellValue(ShowDocStatus(cust.DocumentStatus));
                    
                }else{
                    dataRow.CreateCell(col++).SetCellValue(string.Empty);
                
                }
                //Compute for duration in days CurrentDate - Application Date
                 /*DateTime today = DateTime.Now;
                 DateTime end = Convert.ToDateTime(cust.ApplicationDate.ToString());
                 TimeSpan diff = today-end;*/
                if(cust.LeadAging!=null){
                   dataRow.CreateCell(col++).SetCellValue(cust.LeadAging);
                }else{
                     dataRow.CreateCell(col++).SetCellValue(string.Empty);
                }
                
                if(cust.AppDuration!=null){
                  dataRow.CreateCell(col++).SetCellValue(cust.AppDuration);//for duration
                    
                }else{
                   dataRow.CreateCell(col++).SetCellValue(string.Empty);//for duration
                 
                }
                dataRow.CreateCell(col++).SetCellValue(cust.Email);//for email
               /* string llno=cust.LandLineNo.ToString().Replace("|","");
                string mobileNo =cust.MobileNo.ToString().Replace("|","");
                if(llno=="63"){
                    llno=string.Empty;
                }
                
                if(mobileNo=="63"){
                    mobileNo=string.Empty;
                }*/
                dataRow.CreateCell(col++).SetCellValue(GetPhoneValue(cust.LandLineNo.ToString()));//for landline
                dataRow.CreateCell(col++).SetCellValue(GetPhoneValue(cust.MobileNo.ToString()));//for mobile
                dr++;
            }
            dataRow =sheet.GetRow(dr);
            if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
            
            dataRow.CreateCell(0).SetCellValue("Total Records:");
            dataRow.CreateCell(1).SetCellValue(customers.Count.ToString());
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            //autosize columns
            for(int i=0;i<13;i++){
                sheet.AutoSizeColumn(i,true);
            }
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }
    
     public static byte[] GeneratePAListXL(string path,string sh,IList<dynamic> customers){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=2;
           IRow dataRow=null;
            foreach(var cust in customers){
                int col=0;
               dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0} {1}",cust.FName,cust.LName));
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardAppNo);
                //dataRow.CreateCell(col++).SetCellValue(cust.CustomerStatus);
                string ccsd_duration = UtilityManager.GetDuration(cust.CashCardStatusDate);
                string ccdd_duration = UtilityManager.GetDuration(cust.DelStatusDate);
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0}({1})",ShowCCStatus(cust.CashCardStatus),ccsd_duration));
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0}",ShowDelStatus(cust.CardDelStatus,cust.DelStatusDate)));
            //Compute for duration in days CurrentDate - Application Date
                 /*DateTime today = DateTime.Now;
                 DateTime end = Convert.ToDateTime(cust.ApplicationDate.ToString());
                 TimeSpan diff = today-end;*/
                dataRow.CreateCell(col++).SetCellValue(GetDuration(cust.ApplicationDate));//for duration
                dataRow.CreateCell(col++).SetCellValue(cust.Region);
                dataRow.CreateCell(col++).SetCellValue(cust.Solicitor);
                dataRow.CreateCell(col++).SetCellValue(cust.Country);
                dataRow.CreateCell(col++).SetCellValue(cust.PartnerBank);
                dataRow.CreateCell(col++).SetCellValue(cust.CardDelCourier);
              
                dr++;
            }
            //totals
             dataRow =sheet.GetRow(dr);
            dataRow.CreateCell(0).SetCellValue("Total Records:");
            dataRow.CreateCell(1).SetCellValue(customers.Count.ToString());
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
             //autosize columns
            for(int i=0;i<11;i++){
                sheet.AutoSizeColumn(i,true);
            }
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static byte[] GenerateAppInProgListXL(string path,string sh,IList<dynamic> customers){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=2;
            IRow dataRow=null;
            foreach(var cust in customers){
                int col=0;
               dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0} {1}",cust.FName,cust.LName));
                dataRow.CreateCell(col++).SetCellValue(cust.CashCardAppNo);
                string ccsd_duration = UtilityManager.GetDuration(cust.CashCardStatusDate);
                string ccdd_duration = UtilityManager.GetDuration(cust.DelStatusDate);
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0}({1})",ShowCCStatus(cust.CashCardStatus),ccsd_duration));
                dataRow.CreateCell(col++).SetCellValue(string.Format("{0}",ShowDelStatus(cust.CardDelStatus,cust.DelStatusDate)));
                /* DateTime today = DateTime.Now;
                 DateTime end = Convert.ToDateTime(cust.ApplicationDate.ToString());
                 TimeSpan diff = today-end;*/
                
                dataRow.CreateCell(col++).SetCellValue(GetDuration(cust.ApplicationDate));//for duration
                dataRow.CreateCell(col++).SetCellValue(cust.Region);
                dataRow.CreateCell(col++).SetCellValue(cust.Solicitor);
                //dataRow.CreateCell(col++).SetCellValue(cust.Country);
                dataRow.CreateCell(col++).SetCellValue(cust.PartnerBank);
                dataRow.CreateCell(col++).SetCellValue(cust.CardDelCourier);
                dr++;
            }
            //Totals
             dataRow =sheet.GetRow(dr);
             if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
            
            dataRow.CreateCell(0).SetCellValue("Total Records:");
            dataRow.CreateCell(1).SetCellValue(customers.Count.ToString());
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            
             //autosize columns
            for(int i=0;i<9;i++){
                sheet.AutoSizeColumn(i,true);
            }
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }


     public static byte[] GenerateAuditReportXL(string path,string sh,IList<dynamic> auditLog){
        try{
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=2;
           
            foreach(var audit in auditLog){
                int col=0;
               IRow dataRow =sheet.GetRow(dr);
               if(dataRow==null)
                    dataRow=sheet.CreateRow(dr);
                //get customer details
                
               // var customer = CustomerManager.GetCustomer(audit.CustomerID);
                
                dataRow.CreateCell(col++).SetCellValue(audit.CustomerID);
                dataRow.CreateCell(col++).SetCellValue(audit.CashCardNo);
                dataRow.CreateCell(col++).SetCellValue(audit.FieldChange);
                dataRow.CreateCell(col++).SetCellValue(audit.TypeOfChange);
                dataRow.CreateCell(col++).SetCellValue(audit.OldValue);
                dataRow.CreateCell(col++).SetCellValue(audit.NewValue);
                dataRow.CreateCell(col++).SetCellValue(audit.ChangeBy);
                dataRow.CreateCell(col++).SetCellValue(audit.DateCreated.ToString());
                dr++;
            }
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            
            
           
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
        }catch(Exception e){
            throw e;
        }
    }
    
    public  static string ShowCCStatus(string status){
        var s = RefDataManager.GetCashCardStatLkUpVal();
        string cstat = string.Empty;
        foreach(var cs in s){
            if(cs.RefID.Equals(status)){
                cstat=cs.RefDesc;
            }
        }
        
        return cstat;
     }
                
 public static string ShowDelStatus(string del,DateTime date){
        var d = RefDataManager.GetCardDelLkUpVal();
        string duration = GetDuration(date);
        string cdel = string.Empty;
        string result = string.Empty;
        foreach(var cd in d){
            if(cd.RefID.Equals(del)){
                cdel=cd.RefDesc;
            }
        }
        
         if((string.Compare(cdel.ToLower(),"pending")==0) || (string.Compare(cdel.ToLower(),"delivered")==0)){
                        result = string.Format("{0}",cdel);
                    }else{
                        result = string.Format("{0}({1})",cdel,duration);
                    }
        
        return result;
    }
 public static string ShowDocStatus(string status){
    var ds = RefDataManager.GetDocStatLkUpVal();
    var docStat = string.Empty;
    
    foreach(var d in ds){
        if(d.RefID.Equals(status)){
            docStat = d.RefDesc;        
        }
    }
    
    return docStat;
}
    
    public static byte[] GenerateXLFromTemplate(string path,string sh,IList<dynamic> customers){
        try{
            //set excelTemplate
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=4;
           
            foreach(var cust in customers){
                int col=0;
               //sIRow dataRow =sheet.GetRow(dr++);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.CashCardAppNo);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.CashCardNo);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(string.Empty);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.LName);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.FName);
                //split addr1 and add2
                string[] addr = cust.CurrMailAdd.Split('|');
                
                sheet.GetRow(dr).GetCell(col++).SetCellValue(addr[0]);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(addr[1]);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.CurrMailCity);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.CurrMailCountry);
                //for phone numbers remove the bars
                string lphone = cust.LandLineNo.Replace("|","");
                string mphone = cust.MobileNo.Replace("|","");
                sheet.GetRow(dr).GetCell(col++).SetCellValue(lphone);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(mphone);
                //capitalize gender
                string gender = cust.Gender.ToUpper(new CultureInfo("en-US", false));
                sheet.GetRow(dr).GetCell(col++).SetCellValue(gender);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(DateTime.Parse(cust.Bdate.ToString("MM-dd-yyyy")));
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.PlaceOfBirth);
                //get idtype desc from lookup
                var idTypes = RefDataManager.GetIDTypeLkUpVal();
                string idType=string.Empty;
                foreach(var id in idTypes){
                    if(id.RefID.Equals(cust.IDType)){
                        idType = id.RefDesc;
                    }
                }
                
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.IDType);
                //check ID Type,
                //TIN - 09, SSS-07
                //if ID Type is SSS, Leave the ID Number col blank
                if(cust.IDType!="07"){
                   sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.IDNumber);    
                }
                
                if(cust.IDType=="09"){
                   sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.IDNumber);
                }else{
                   sheet.GetRow(dr).GetCell(col++).SetCellValue(string.Empty);
                }
                if(cust.IDType=="07"){
                   sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.IDNumber);
                }else{
                   sheet.GetRow(dr).GetCell(col++).SetCellValue(string.Empty);
                }
                //get nature of work description from reference data manager
                string now_desc=string.Empty;
                var now = RefDataManager.GetEmpTypeLkUpVal();
                 string[] emps = cust.Employment.Split(',');
                string emp = string.Empty;
                if(emp!=null){
                    emp=emps[0];
                }
                
                foreach(var w in now){
                    if(w.RefId.Equals(emp)){
                        now_desc=w.RefDesc;
                    }
                }
                //occupation description should be from nature of work
               
                sheet.GetRow(dr).GetCell(col++).SetCellValue(now_desc);
                //get code for Nature of Work in Reference Data
                
                //sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.NatureOfWork);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(emp);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.Employer);
                sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.Nationality);
                //get only the first srcofFunds
                string[] sofs = cust.SrcOfFunds.Split(',');
                string sof = string.Empty;
                if(sof!=null){
                    sof = sofs[0];
                }
                sheet.GetRow(dr).GetCell(col++).SetCellValue(sof);
                dr++;
            }
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            
            
           
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
            
        }catch(Exception e){
            throw e;
        }
    }
    
     public static byte[] GenerateXLFromTemplate2(string path,string sh,IList<dynamic> customers){
        try{
            //set excelTemplate
            ExcelManager.Template = path;
            HSSFWorkbook wb = ExcelManager.InitializeWB("MyRemit Cash Card");
            //Getting worksheet by name
            ISheet sheet= wb.GetSheet(sh);
            //Get the row
            int dr=4;
            //format for bday
             HSSFDataFormat format = (HSSFDataFormat)wb.CreateDataFormat();
            //get the style of the first row
            IRow dataRow =sheet.GetRow(dr);
             if(dataRow==null)
                    dataRow = sheet.CreateRow(dr);
                
                //get the cellStyle
                HSSFCell origCell = (HSSFCell)dataRow.GetCell(0);
                if(origCell==null)
                    origCell = (HSSFCell)dataRow.CreateCell(0);
                
                HSSFCellStyle origCellStyle = (HSSFCellStyle)origCell.CellStyle;
           
            foreach(var cust in customers){
                int col=0;
                IRow dataRow2 =sheet.GetRow(dr);
                if(dataRow2==null)
                    dataRow2 = sheet.CreateRow(dr);
                
                HSSFCell ccAppNo = (HSSFCell)dataRow2.CreateCell(col++);
                ccAppNo.CellStyle=origCellStyle;
                ccAppNo.SetCellValue(cust.CashCardAppNo);
                
                HSSFCell ccNo = (HSSFCell)dataRow2.CreateCell(col++);
                ccNo.CellStyle = origCellStyle;
                ccNo.SetCellValue(cust.CashCardNo);
                
                HSSFCell accNo = (HSSFCell)dataRow2.CreateCell(col++);
                accNo.CellStyle = origCellStyle;
                accNo.SetCellValue(string.Empty);
                
                HSSFCell lName = (HSSFCell)dataRow2.CreateCell(col++);
                lName.CellStyle = origCellStyle;
                lName.SetCellValue(cust.LName);
                
                HSSFCell fName = (HSSFCell)dataRow2.CreateCell(col++);
                fName.CellStyle = origCellStyle;
                fName.SetCellValue(cust.FName);
                
                //split addr1 and add2
                string[] addr = cust.CurrMailAdd.Split('|');
              
                HSSFCell add1 = (HSSFCell)dataRow2.CreateCell(col++);
                add1.CellStyle = origCellStyle;
                add1.SetCellValue(addr[0]);
                
                HSSFCell add2 = (HSSFCell)dataRow2.CreateCell(col++);
                add2.CellStyle = origCellStyle;
                add2.SetCellValue(addr[1]);
               
                HSSFCell cMCity = (HSSFCell)dataRow2.CreateCell(col++);
                cMCity.CellStyle = origCellStyle;
                cMCity.SetCellValue(cust.CurrMailCity);
                
                HSSFCell cMCountry = (HSSFCell)dataRow2.CreateCell(col++);
                cMCountry.CellStyle = origCellStyle;
                cMCountry.SetCellValue(cust.CurrMailCountry);
                
                //for phone numbers remove the bars
                string lphone = cust.LandLineNo.Replace("|","");
                string mphone = cust.MobileNo.Replace("|","");
                
                HSSFCell lPhone = (HSSFCell)dataRow2.CreateCell(col++);
                lPhone.CellStyle = origCellStyle;
                lPhone.SetCellValue(lphone);
                
                HSSFCell mPhone = (HSSFCell)dataRow2.CreateCell(col++);
                mPhone.CellStyle = origCellStyle;
                mPhone.SetCellValue(mphone);
                 
                //capitalize gender
                string gender = cust.Gender.ToUpper(new CultureInfo("en-US", false));
                
                HSSFCell gndr = (HSSFCell)dataRow2.CreateCell(col++);
                gndr.CellStyle = origCellStyle;
                gndr.SetCellValue(gender);
                 
                HSSFCell bdate = (HSSFCell)dataRow2.CreateCell(col++);
                bdate.CellStyle = origCellStyle;
                
                bdate.CellStyle.DataFormat=format.GetFormat("yyyymmdd");
                bdate.SetCellValue(DateTime.Parse(cust.Bdate.ToString()));
                
                HSSFCell pob = (HSSFCell)dataRow2.CreateCell(col++);
                pob.CellStyle = origCellStyle;
                pob.SetCellValue(cust.PlaceOfBirth);
                
                //get idtype desc from lookup
                var idTypes = RefDataManager.GetIDTypeLkUpVal();
                string idType=string.Empty;
                foreach(var id in idTypes){
                    if(id.RefID.Equals(cust.IDType)){
                        idType = id.RefDesc;
                    }
                }
                
                HSSFCell idT = (HSSFCell)dataRow2.CreateCell(col++);
                idT.CellStyle = origCellStyle;
                if(string.Compare(cust.IDType,"default")==0){
                    idT.SetCellValue(string.Empty);
                }else{
                   idT.SetCellValue(cust.IDType); 
                }
                
                
                //check ID Type,
                //TIN - 09, SSS-07
                //if ID Type is SSS, Leave the ID Number col blank
                if(cust.IDType!="07"){
                    HSSFCell idN = (HSSFCell)dataRow2.CreateCell(col++);
                    idN.CellStyle = origCellStyle;
                    idN.SetCellValue(cust.IDNumber);
                 }else{
                    HSSFCell idN = (HSSFCell)dataRow2.CreateCell(col++);
                    idN.CellStyle = origCellStyle;
                    idN.SetCellValue(string.Empty);
                }
                
                if(cust.IDType=="09"){
                   HSSFCell idTIN = (HSSFCell)dataRow2.CreateCell(col++);
                   idTIN.CellStyle = origCellStyle;
                   idTIN.SetCellValue(cust.IDNumber);
               
                }else{
                   HSSFCell idTIN = (HSSFCell)dataRow2.CreateCell(col++);
                   idTIN.CellStyle = origCellStyle;
                   idTIN.SetCellValue(string.Empty);
                }
                
                if(cust.IDType=="07"){
                   HSSFCell idSSS = (HSSFCell)dataRow2.CreateCell(col++);
                   idSSS.CellStyle = origCellStyle;
                   idSSS.SetCellValue(cust.IDNumber);
                }else{
                   HSSFCell idSSS = (HSSFCell)dataRow2.CreateCell(col++);
                   idSSS.CellStyle = origCellStyle;
                   idSSS.SetCellValue(string.Empty);
                }
                //get nature of work description from reference data manager
                string now_desc=string.Empty;
                var now = RefDataManager.GetEmpTypeLkUpVal();
                 string[] emps = cust.Employment.Split(',');
                string emp = string.Empty;
                if(emp!=null){
                    emp=emps[0];
                }
                
                foreach(var w in now){
                    if(w.RefId.Equals(emp)){
                        now_desc=w.RefDesc;
                    }
                }
                //occupation description should be from Nature of Work and not from Occupation Desc
                var nw = RefDataManager.GetNatureOfWorkLkUpVal();
                string nw_desc=string.Empty;
                foreach(var n in nw){
                    if(n.RefId.Equals(cust.NatureOfWork)){
                        nw_desc=n.RefDesc;
                    }
                }
                
                HSSFCell nowCell = (HSSFCell)dataRow2.CreateCell(col++);
                nowCell.CellStyle = origCellStyle;
                //nowCell.SetCellValue(now_desc);
                nowCell.SetCellValue(nw_desc);
           
              
                //get code for Nature of Work in Reference Data
               
                //sheet.GetRow(dr).GetCell(col++).SetCellValue(cust.NatureOfWork);
                HSSFCell empCell = (HSSFCell)dataRow2.CreateCell(col++);
                empCell.CellStyle = origCellStyle;
                empCell.SetCellValue(emp);
                
                HSSFCell empCell2 = (HSSFCell)dataRow2.CreateCell(col++);
                empCell2.CellStyle = origCellStyle;
                empCell2.SetCellValue(cust.Employer);
                
                HSSFCell nat = (HSSFCell)dataRow2.CreateCell(col++);
                nat.CellStyle = origCellStyle;
                nat.SetCellValue(cust.Nationality);
           
                
                //get only the first srcofFunds
                string[] sofs = cust.SrcOfFunds.Split(',');
                string sof = string.Empty;
                if(sof!=null){
                    sof = sofs[0];
                }
                
                HSSFCell sofCell = (HSSFCell)dataRow2.CreateCell(col++);
                sofCell.CellStyle = origCellStyle;
                sofCell.SetCellValue(sof);
           
                
                dr++;
            }
            //if cell has calculations
            sheet.ForceFormulaRecalculation=true;
            
            
           
            //Write workbook content to the filesstream
            byte[] data = ExcelManager.WriteToStream(wb).ToArray();
            //
            return data;
            
        }catch(Exception e){
            throw e;
        }
    }
   
    
    public static void MoveFile(string source,string dest){
        try{
           //File.Move(source, dest);
            File.Copy(source,dest,true);
        }catch(Exception e){
            throw e;
        }
    }
    
    public static bool FileExists(string path){
        try{
            return File.Exists(path);
        }catch(Exception e){
            throw e;
        }
    }
    
    public static void DeleteFile(string path){
        try{
            File.Delete(path);
        }catch(Exception e){
            throw e;
        }
    }
    
    public static string GetFileFromUploadDir(string path){
        try{
            string[] filePaths = Directory.GetFiles(path, "*.xls");
            if(filePaths.Length>0)
              return filePaths[0];
            else
                return null;
        }catch(Exception e){
            throw e;
        }
        
    }
    
    public static string GetCounterForFileExt(string path){
        DateTime startOfDay = DateTime.Today;
        DateTime current = DateTime.Now;
        DateTime odate=current;
         int ctrVal=0;
        //get the old date in file
        string[] old_date = File.ReadAllLines(path);
        if(old_date.Length>1){
            ctrVal=Convert.ToInt32(old_date[0]);
            odate=Convert.ToDateTime(old_date[1]);
        }
         
       
       // int result = DateTime.Compare(startOfDay.,odate.Date);
        if(startOfDay.Date==odate.Date){
           ctrVal++;
           File.WriteAllText(path,ctrVal.ToString()+ "\n"+ startOfDay); 
        }else{
            //read the file
           /* string[] ctr=File.ReadAllLines(path);
            ctrVal = Convert.ToInt32(ctr[0]);*/
            ctrVal=0;
            ++ctrVal;
            File.WriteAllText(path,ctrVal.ToString() + "\n" + startOfDay);
        }
        
        return string.Format("{0:d2}",ctrVal);
    }
    
    public static byte[] FileToByteArray(string fileName){
        byte[] buffer=null;
        try{
            FileStream fs = new FileStream(fileName,FileMode.Open,FileAccess.Read);
            BinaryReader br = new BinaryReader(fs);
            long totBytes = new FileInfo(fileName).Length;
            //read entire file to buffer
            buffer = br.ReadBytes((Int32)totBytes);
            fs.Close();
            br.Close();
            
            return buffer;
        }catch(Exception e){
            throw e;
        }
        
        
    }
    
    public static string GetVal(string param){
        return (param!=null)?param:"";
        
    }
    
    public static DateTime? GetDateVal(string date){
        return (date!="")?Convert.ToDateTime(date):(DateTime?)null;
    }
    
    public static DateTime? GetDefaultDateIfNull(DateTime? date){
        return (date==null)?new DateTime(1900,1,1):date;
    }
    
    public static DateTime? GetDefaultDate(){
        return new DateTime(1900,1,1);
    }
    
    public static string GetDuration(DateTime date){
         DateTime today = DateTime.Now;
         TimeSpan diff = today-date;
         
         return diff.Days.ToString();
    }
    
    public static string GetPhoneValue(string phone){
         string phoneVal=phone.Replace("|","");
         if(phoneVal=="63"){
              phoneVal=string.Empty;
          }
          return phoneVal;      
          
    }
    
    private void _concatAndAddContent(List<string> pdf) {
           Document document = new Document(); 
            MemoryStream output = new MemoryStream();
           
            PdfCopy copy = new PdfCopy(document, output);
            document.Open();
            PdfImportedPage page;
            // this object **REQUIRED** to add content when concatenating PDFs
            PdfCopy.PageStamp stamp;
            // image watermark/background 
          
            // set transparency
            PdfGState state = new PdfGState();
            state.FillOpacity = 0.3F;
            state.StrokeOpacity = 0.3F;   
            
            foreach (string p in pdf) {
                string pPath = string.Format("cat/{0}.pdf", p);
                PdfReader reader = new PdfReader(new RandomAccessFileOrArray(pPath), null);    
                int pages = reader.NumberOfPages;
            // loop over document pages
                for (int i = 0; i < pages; ) {
                    page = copy.GetImportedPage(reader, ++i);
                    stamp = copy.CreatePageStamp(page);
                    PdfContentByte cb = stamp.GetUnderContent();
                    cb.SaveState();
                    cb.SetGState(state);
                    stamp.AlterContents();
                    copy.AddPage(page);
                }
            }
            document.Close();
}
    
    public static void AddChangeFields(Dictionary<string,Dictionary<string,string>> changeList,string field,string oldVal,string newVal){
       
        try{
           Dictionary<string,string> values= new Dictionary<string,string>();
           values.Add(oldVal,newVal); 
           changeList.Add(field,values);  
          
        }catch(Exception e){
            throw e;
        }
    }
    
    
    
}
